﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Globalization;
using System.Data.SqlClient;
using System.Runtime.InteropServices;

//SSIS
using Microsoft.SqlServer.Dts.Runtime; //ControlFlow - Managed
using Microsoft.SqlServer.Dts.Pipeline; //DataFlow - Managed
using Microsoft.SqlServer.Dts.Pipeline.Wrapper; //Dataflow - Interop

using CommandLine;//CLP

namespace BA.SqlServer.Ssis.Dfl
{
    public class DFLDiscoverer
    {
        String _packagePath;
        String _server;
        String _database;
        String _user;
        String _pwd;
        String _packageID;
        String _packageName;
        int _dflCount;
        int _cfCount;
        DFLMetaDataContext _datacontext;


        #region Constructors

        public DFLDiscoverer(CLPArguments args)
        {
            try
            {
                this.PackagePath = args.package;
                this.Server = args.server;
                this.Database = args.database;
                this.User = args.user;
                this.Password = args.pwd;


                this.DataContext = this.SetDataContext();
                this.PackageID = this.GetPackageID();
                this.PackageName = this.GetPackageName();
                this.DeletePackageDFLMetaData(this.PackageID);
                this.DFLCount = 0;
            }
            catch (NullReferenceException nullRefException)
            { throw nullRefException; }
            catch (ArgumentException argException)
            { throw argException; }
            catch (DtsRuntimeException ssisRuntimeException)
            { throw ssisRuntimeException; }
            catch (SqlException sqlException)
            { throw sqlException; }
            catch (DtsPipelineException ssisDataflowException)
            { throw ssisDataflowException; }
            catch (DtsTaskException ssisTaskException)
            { throw ssisTaskException; }
            catch (DtsComponentException ssisComponentException)
            { throw ssisComponentException; }
            catch (DtsComException ssisComException)
            { throw ssisComException; }
            catch (DtsException ssisException)
            { throw ssisException; }


        }

        #endregion

        #region Properties
        private String PackagePath { get { return _packagePath; } set { _packagePath = value; } }

        private String Server { get { return _server; } set { _server = value; } }

        private String Database { get { return _database; } set { _database = value; } }

        private String User { get { return _user; } set { _user = value; } }

        private String Password { get { return _pwd; } set { _pwd = value; } }

        private String PackageID { get { return _packageID; } set { _packageID = value; } }

        private String PackageName { get { return _packageName; } set { _packageName = value; } }

        private DFLMetaDataContext DataContext { get { return _datacontext; } set { _datacontext = value; } }

        public int DFLCount { get { return _dflCount; } set { _dflCount = value; } }
        public int CFCount { get { return _cfCount; } set { _cfCount = value; } }

        #endregion


        #region Methods
        /// <summary>
        /// Returns an instance of DFLMetaDataContext object 
        /// </summary>
        /// <returns>DFLMetaDataContext, if connection is successful</returns>
        private DFLMetaDataContext SetDataContext()
        {
            String ConnectionString;

            if (this.User != null && this.Password != null) //sql server authentication
                ConnectionString = "Data Source=" + this.Server + ";Initial Catalog=" + this.Database + ";User ID=" + this.User + ";Password=" + this.Password + ";";
            else //windows authentication
                ConnectionString = "Data Source=" + this.Server + ";Initial Catalog=" + this.Database + ";Integrated Security=True;";

            DFLMetaDataContext dflDB = new DFLMetaDataContext(ConnectionString);
            if (dflDB.DatabaseExists())
                _datacontext = dflDB;

            return _datacontext;
        }

        //Method to get PackageID
        /// <summary>
        /// Returns the ID of Package
        /// </summary>
        /// <exception cref="NullReferenceException"></exception>
        /// <exception cref="ArgumentException"></exception>
        /// <exception cref="DtsRuntimeException"></exception>
        /// <exception cref="InvalidOperationException"></exception>
        /// <exception cref="StackOverflowException"></exception>
        /// <returns>PackageID</returns>
        private String GetPackageID()
        {
            Application ssisApp;
            Package ssisPackage;
            try
            {
                ssisApp = new Application();
                ssisPackage = ssisApp.LoadPackage(this.PackagePath, null);
            }
            catch (NullReferenceException nullRefException)
            { throw new NullReferenceException("ERROR: Unable to get package ID. GetPackageID() exited with exception " + nullRefException.Message); }
            catch (ArgumentException argException)
            { throw new ArgumentException("ERROR: Unable to get package ID. Verify the package path " + this.PackagePath + ". GetPackageID() exited with exception " + argException.Message); }
            catch (DtsRuntimeException ssisRuntimeException)
            { throw ssisRuntimeException; }
            catch (InvalidOperationException invalidOpException)
            { throw invalidOpException; }
            catch (StackOverflowException stackOverflowException)
            { throw stackOverflowException; }


            return ssisPackage.ID;
        }

        //Method to get Package Name
        /// <summary>
        /// Returns the Name of Package
        /// </summary>
        /// <exception cref="NullReferenceException"></exception>
        /// <exception cref="ArgumentException"></exception>
        /// <exception cref="DtsRuntimeException"></exception>
        /// <exception cref="InvalidOperationException"></exception>
        /// <exception cref="StackOverflowException"></exception>
        /// <returns>PackageName</returns>
        private String GetPackageName()
        {
            Application ssisApp;
            Package ssisPackage;
            try
            {
                ssisApp = new Application();
                ssisPackage = ssisApp.LoadPackage(this.PackagePath, null);
            }
            catch (NullReferenceException nullRefException)
            { throw new NullReferenceException("ERROR: Unable to get package ID. GetPackageID() exited with exception " + nullRefException.Message); }
            catch (ArgumentException argException)
            { throw new ArgumentException("ERROR: Unable to get package ID. Verify the package path " + this.PackagePath + ". GetPackageID() exited with exception " + argException.Message); }
            catch (DtsRuntimeException ssisRuntimeException)
            { throw ssisRuntimeException; }
            catch (InvalidOperationException invalidOpException)
            { throw invalidOpException; }
            catch (StackOverflowException stackOverflowException)
            { throw stackOverflowException; }


            return ssisPackage.Name;
        }

        /// <summary>
        /// Deletes DFL metadata of the package if it already exists
        /// </summary>
        /// <param name="packageID">Package ID</param>
        /// <exception cref="NullReferenceException"></exception>
        /// <exception cref="SqlException"></exception>
        /// <returns>1 on success</returns>
        private int DeletePackageDFLMetaData(string packageID)
        {
            var dflMetadatas = from metadatas in this.DataContext.DFLMetaDatas
                               where metadatas.PackageID == packageID
                               select metadatas;
            try
            {
                this.DataContext.DFLMetaDatas.DeleteAllOnSubmit(dflMetadatas);
                this.DataContext.SubmitChanges();
            }
            catch (NullReferenceException nullRefException)
            { throw new NullReferenceException("ERROR: Unable to delete existing metadata for " + packageID + ". DeletePackageDFLMetaData() exited with exception " + nullRefException.Message); }
            catch (SqlException sqlException)
            { throw sqlException; }

            return 1;
        }


        //Method that discovers DFL Metadata and writes to database
        public int Discover()
        {
            int recCount = 0;
            try
            {
                Application ssisApp = new Application(); //SSIS Application
                Package ssisPackage = ssisApp.LoadPackage(this.PackagePath, null); //SSIS Package

                Executables executables = ssisPackage.Executables; //ControlFlow Components
                foreach (Executable exec in executables)
                {
                    String objectType = exec.GetType().Name;

                    //DataFlow task in ControlFlow
                    if (objectType == "TaskHost") //Check if the ControlFlow Component is a Task
                    {
                        TaskHost taskhost = exec as TaskHost;
                        if (taskhost.InnerObject.ToString() == "System.__ComObject") //Check if the Task is a DataFlow component
                            recCount += this.DiscoverDFLMetaData(ref taskhost);
                    }

                    //DataFlow tasks within Sequence Containers in ControlFlow (including Dataflows in nested containers)
                    if (objectType == "Sequence")
                    {
                        Sequence Seq = exec as Sequence;
                        recCount += this.DiscoverDFLMetaDataSeq(ref Seq);
                    }

                    if (objectType == "ForEachLoop")
                    {
                        ForEachLoop feLoop = exec as ForEachLoop;
                        recCount += this.DiscoverDFLMetaDataFELoop(ref feLoop);
                    }

                    if (objectType == "ForLoop")
                    {
                        ForLoop fLoop = exec as ForLoop;
                        recCount += this.DiscoverDFLMetaDataFLoop(ref fLoop);

                    }
                    this.CFCount++;
                } //End - ControlFlow Components
            }
            catch (NullReferenceException nullRefException)
            { throw nullRefException; }
            catch (InvalidOperationException invalidOpException)
            { throw invalidOpException; }
            catch (StackOverflowException stackOverflowException)
            { throw stackOverflowException; }
            catch (SqlException sqlException)
            { throw sqlException; }
            catch (DtsPipelineException ssisDataflowException)
            { throw ssisDataflowException; }
            catch (DtsTaskException ssisTaskException)
            { throw ssisTaskException; }
            catch (DtsComponentException ssisComponentException)
            { throw ssisComponentException; }
            catch (DtsComException ssisComException)
            { throw ssisComException; }
            catch (DtsException ssisException)
            { throw ssisException; }
            catch (COMException COMException)
            { throw COMException; }
            return recCount;
        }

        /// <summary>
        /// Examines a Dataflow task and writes metadata info for all input and output columns
        /// </summary>
        /// <exception cref="InvalidOperationException"></exception>
        /// <exception cref="StackOverflowException"></exception>
        /// <exception cref="SqlException"></exception>
        /// <param name="TaskHost">Control Flow Task of type Taskhost</param>
        /// <returns>Number of dataflow elements discovered and written to DFLMetadata table</returns>
        private int DiscoverDFLMetaData(ref TaskHost TaskHost)
        {
            int count = 0;
            List<DFLMetaData> dflMetadataList = new List<DFLMetaData>();
            try
            {
                MainPipe mainpipe = TaskHost.InnerObject as MainPipe; //DataFlow Control

                IDTSComponentMetaDataCollection100 compMetadatas = mainpipe.ComponentMetaDataCollection;
                foreach (IDTSComponentMetaData100 compMetadata in compMetadatas) //Dataflow Components
                {

                    IDTSInputCollection100 inputs = compMetadata.InputCollection;
                    foreach (IDTSInput100 input in inputs) //Inputs
                    {
                        IDTSInputColumnCollection100 columns = input.InputColumnCollection;
                        foreach (IDTSInputColumn100 column in columns) //Input Columns
                        {
                            count++;
                            DFLMetaData dflMetadata = new DFLMetaData();

                            dflMetadata.PackageID = this.PackageID;
                            dflMetadata.PackageName = this.PackageName;
                            dflMetadata.DataflowID = TaskHost.ID;
                            dflMetadata.DataflowName = TaskHost.Name;
                            dflMetadata.IOType = 'I';
                            dflMetadata.IOName = input.Name;
                            dflMetadata.IOID = input.ID;
                            dflMetadata.ColumnName = column.Name;
                            dflMetadata.ColumnDesc = column.Description;
                            dflMetadata.LineageID = column.LineageID;
                            dflMetadata.ColumnID = column.ID;
                            dflMetadata.ColumnDataType = column.DataType.ToString();
                            dflMetadata.ColumnPrecision = column.Precision;
                            dflMetadata.ColumnScale = column.Scale;
                            dflMetadata.UpstreamComponentName = column.UpstreamComponentName;
                            dflMetadata.SortKeyPosition = column.SortKeyPosition;
                            dflMetadata.ColumnCodePage = column.CodePage;
                            dflMetadata.MappedColumnID = column.MappedColumnID;
                            dflMetadata.UsageType = column.UsageType.ToString();
                            dflMetadata.ColumnType = column.ObjectType.ToString();

                            dflMetadataList.Add(dflMetadata);
                        } //End - Input Columns
                    } //End - Inputs

                    //------------------------------
                    IDTSOutputCollection100 outputs = compMetadata.OutputCollection;
                    foreach (IDTSOutput100 output in outputs) //Outputs
                    {
                        IDTSOutputColumnCollection100 columns = output.OutputColumnCollection;
                        foreach (IDTSOutputColumn100 column in columns) //Output Columns
                        {
                            count++;
                            DFLMetaData dflMetadata = new DFLMetaData();

                            dflMetadata.PackageID = this.PackageID;
                            dflMetadata.PackageName = this.PackageName;
                            dflMetadata.DataflowID = TaskHost.ID;
                            dflMetadata.DataflowName = TaskHost.Name;
                            dflMetadata.IOType = 'O';
                            dflMetadata.IOName = output.Name;
                            dflMetadata.IOID = output.ID;
                            dflMetadata.ColumnName = column.Name;
                            dflMetadata.ColumnDesc = column.Description;
                            dflMetadata.LineageID = column.LineageID;
                            dflMetadata.ColumnID = column.ID;
                            dflMetadata.ColumnDataType = column.DataType.ToString();
                            dflMetadata.ColumnPrecision = column.Precision;
                            dflMetadata.ColumnScale = column.Scale;
                            dflMetadata.UpstreamComponentName = null;
                            dflMetadata.SortKeyPosition = column.SortKeyPosition;
                            dflMetadata.ColumnCodePage = column.CodePage;
                            dflMetadata.MappedColumnID = column.MappedColumnID;
                            dflMetadata.UsageType = null;
                            dflMetadata.ColumnType = column.ObjectType.ToString();

                            dflMetadataList.Add(dflMetadata);

                        } //End - Output Columns
                    } //End - Outputs
                } //End - Dataflow Components


                this.DataContext.DFLMetaDatas.InsertAllOnSubmit(dflMetadataList);
                this.DataContext.SubmitChanges();

                this.DFLCount++;

            }
            catch (NullReferenceException nullRefException)
            {  //do nothing - could be because the taskhost is a pipeline component e.g excecute SSIS Package Component
                return 0;
            }
            catch (InvalidOperationException invalidOpException)
            { throw new InvalidOperationException("ERROR: Invalid Operation. DiscoverDFLMetaData() exited with exception " + invalidOpException.Message); }
            catch (StackOverflowException stackOverflowException)
            { throw stackOverflowException; }
            catch (SqlException sqlException)
            { throw sqlException; }
            catch (DtsPipelineException ssisDataflowException)
            { throw ssisDataflowException; }
            catch (DtsTaskException ssisTaskException)
            { throw ssisTaskException; }
            catch (DtsComponentException ssisComponentException)
            { throw ssisComponentException; }
            catch (DtsComException ssisComException)
            { throw ssisComException; }
            catch (DtsException ssisException)
            { throw ssisException; }
            catch (COMException COMException)
            { throw new COMException("ERROR : Package metadata is incomplete. Compile your package before trying again. DiscoverDFLMetaData() exited with exception " + COMException.Message); }


            return count;
        }


        /// <summary>
        /// Recursively discover DataFlow MetaData in a Sequence Container upto any level of nesting
        /// </summary>
        /// <exception cref="NullReferenceException"></exception>
        /// <exception cref="InvalidOperationException"></exception>
        /// <exception cref="StackOverflowException"></exception>
        /// <param name="Seq">Sequence Container Object (byref)</param>
        /// <seealso cref="DiscoverDFLMetaData"/>
        /// <seealso cref="DiscoverDFLMetaDataFELoop"/>
        /// <seealso cref="DiscoverDFLMetaDataFLoop"/>
        /// <returns>Number of dataflow columns discovered and written to DFLMetadata table</returns>
        private int DiscoverDFLMetaDataSeq(ref Sequence seq)
        {
            int count = 0;
            try
            {
                foreach (Executable seqExec in seq.Executables) // ControlFlow Components in Sequence Container
                {
                    String seqObjectType = seqExec.GetType().Name;
                    switch (seqObjectType)
                    {
                        case "TaskHost": //Check if the Seq Container Component is a Task
                            TaskHost taskhost = seqExec as TaskHost;
                            if (taskhost.InnerObject.ToString() == "System.__ComObject") //Check if the Task is a DataFlow component
                                count += this.DiscoverDFLMetaData(ref taskhost);
                            break;
                        case "ForEachLoop": //ForEachLoop Container
                            ForEachLoop feLoop = seqExec as ForEachLoop;
                            count += this.DiscoverDFLMetaDataFELoop(ref feLoop);
                            break;
                        case "ForLoop": //ForLoop Container
                            ForLoop fLoop = seqExec as ForLoop;
                            count += this.DiscoverDFLMetaDataFLoop(ref fLoop);
                            break;
                        case "Sequence": //Recursive call for nested Sequence Containers
                            Sequence nestedSeq = seqExec as Sequence;
                            count += this.DiscoverDFLMetaDataSeq(ref nestedSeq);
                            break;
                    }
                } //End - ControlFlow Components in Sequence Container
            }
            catch (NullReferenceException nullRefException)
            { throw new NullReferenceException("ERROR: Object Instantiation failed. DiscoverDFLMetaDataSeq() exited with Exception " + nullRefException.Message); }
            catch (InvalidOperationException invalidOpException)
            { throw new InvalidOperationException("ERROR: Invalid Operation. DiscoverDFLMetaDataSeq() exited with exception " + invalidOpException.Message); }
            catch (StackOverflowException stackOverflowException)
            { throw stackOverflowException; }
            catch (DtsPipelineException ssisDataflowException)
            { throw ssisDataflowException; }
            catch (DtsTaskException ssisTaskException)
            { throw ssisTaskException; }
            catch (DtsComponentException ssisComponentException)
            { throw ssisComponentException; }
            catch (DtsComException ssisComException)
            { throw ssisComException; }
            catch (DtsException ssisException)
            { throw ssisException; }
            catch (COMException COMException)
            { throw new COMException("ERROR : Package metadata is incomplete. Compile your package before trying again. DiscoverDFLMetaDataSeq() exited with exception " + COMException.Message); }

            return count;

        }



        /// <summary>
        /// Recursively discovers DataFlow MetaData in a ForEachLoop Container upto any level of nesting
        /// </summary>
        /// <exception cref="NullReferenceException"></exception>
        /// <exception cref="InvalidOperationException"></exception>
        /// <exception cref="StackOverflowException"></exception>
        /// <param name="FELoop">For Each Loop Container Object (byref)</param>
        /// <seealso cref="DiscoverDFLMetaData"/>
        /// <seealso cref="DiscoverDFLMetaDataFELoop"/>
        /// <seealso cref="DiscoverDFLMetaDataFLoop"/>
        /// <returns>Number of dataflow columns discovered and written to DFLMetadata table</returns>
        private int DiscoverDFLMetaDataFELoop(ref ForEachLoop feLoop)
        {
            int count = 0;
            try
            {
                foreach (Executable feLoopExec in feLoop.Executables) // ControlFlow Components in ForEachLoop Container
                {

                    String feLoopObjectType = feLoopExec.GetType().Name;
                    switch (feLoopObjectType)
                    {
                        case "TaskHost": //Check if the Seq Container Component is a Task
                            TaskHost taskhost = feLoopExec as TaskHost;
                            if (taskhost.InnerObject.ToString() == "System.__ComObject") //Check if the Task is a DataFlow component
                                count += this.DiscoverDFLMetaData(ref taskhost);
                            break;
                        case "Sequence": //Sequence Container
                            Sequence seq = feLoopExec as Sequence;
                            count += this.DiscoverDFLMetaDataSeq(ref seq);
                            break;
                        case "ForLoop": //ForLoop Container
                            ForLoop floop = feLoopExec as ForLoop;
                            count += this.DiscoverDFLMetaDataFLoop(ref floop);
                            break;
                        case "ForEachLoop": //Recursive call to ForEachLoop Container
                            ForEachLoop nestedFELoop = feLoopExec as ForEachLoop;
                            count += this.DiscoverDFLMetaDataFELoop(ref nestedFELoop);
                            break;
                    }
                } //End - ControlFlow Components in Sequence Container
            }
            catch (NullReferenceException nullRefException)
            { throw new NullReferenceException("ERROR: Object Instantiation failed. DiscoverDFLMetaDataFELoop() exited with Exception " + nullRefException.Message); }
            catch (InvalidOperationException invalidOpException)
            { throw new InvalidOperationException("ERROR: Invalid Operation. DiscoverDFLMetaDataFELoop() exited with exception " + invalidOpException.Message); }
            catch (StackOverflowException stackOverflowException)
            { throw stackOverflowException; }
            catch (DtsPipelineException ssisDataflowException)
            { throw ssisDataflowException; }
            catch (DtsTaskException ssisTaskException)
            { throw ssisTaskException; }
            catch (DtsComponentException ssisComponentException)
            { throw ssisComponentException; }
            catch (DtsComException ssisComException)
            { throw ssisComException; }
            catch (DtsException ssisException)
            { throw ssisException; }
            catch (COMException COMException)
            { throw new COMException("ERROR : Package metadata is incomplete. Compile your package before trying again. DiscoverDFLMetaDataFELoop() exited with exception " + COMException.Message); }


            return count;
        }

        /// <summary>
        /// Recursively discovers DataFlow MetaData in a ForLoop Container upto any level of nesting
        /// </summary>
        /// <exception cref="NullReferenceException"></exception>
        /// <exception cref="InvalidOperationException"></exception>
        /// <exception cref="StackOverflowException"></exception>
        /// <param name="FLoop">For Loop Container Object (byref)</param>
        /// <seealso cref="DiscoverDFLMetaData"/>
        /// <seealso cref="DiscoverDFLMetaDataFELoop"/>
        /// <seealso cref="DiscoverDFLMetaDataFLoop"/>
        /// <returns>Number of dataflow columns discovered and written to DFLMetadata table</returns>
        private int DiscoverDFLMetaDataFLoop(ref ForLoop fLoop)
        {
            int count = 0;
            try
            {
                foreach (Executable fLoopExec in fLoop.Executables) // ControlFlow Components in ForLoop Container
                {

                    String fLoopObjectType = fLoopExec.GetType().Name;
                    switch (fLoopObjectType)
                    {
                        case "TaskHost": //Check if the Seq Container Component is a Task
                            TaskHost taskhost = fLoopExec as TaskHost;
                            if (taskhost.InnerObject.ToString() == "System.__ComObject") //Check if the Task is a DataFlow component
                                count += this.DiscoverDFLMetaData(ref taskhost);
                            break;
                        case "Sequence": //Sequence Container
                            Sequence seq = fLoopExec as Sequence;
                            count += this.DiscoverDFLMetaDataSeq(ref seq);
                            break;
                        case "ForEachLoop": //ForEachLoop Container
                            ForEachLoop feLoop = fLoopExec as ForEachLoop;
                            count += this.DiscoverDFLMetaDataFELoop(ref feLoop);
                            break;
                        case "ForLoop": //Recursive call to ForLoop Container
                            ForLoop nestedFLoop = fLoopExec as ForLoop;
                            count += this.DiscoverDFLMetaDataFLoop(ref nestedFLoop);
                            break;
                    }
                } //End - ControlFlow Components in Sequence Container
            }
            catch (NullReferenceException nullRefException)
            { throw new NullReferenceException("ERROR: Object Instantiation failed. DiscoverDFLMetaDataFLoop() exited with Exception " + nullRefException.Message); }
            catch (InvalidOperationException invalidOpException)
            { throw new InvalidOperationException("ERROR: Invalid Operation. DiscoverDFLMetaDataFLoop() exited with exception " + invalidOpException.Message); }
            catch (StackOverflowException stackOverflowException)
            { throw stackOverflowException; }
            catch (DtsPipelineException ssisDataflowException)
            { throw ssisDataflowException; }
            catch (DtsTaskException ssisTaskException)
            { throw ssisTaskException; }
            catch (DtsComponentException ssisComponentException)
            { throw ssisComponentException; }
            catch (DtsComException ssisComException)
            { throw ssisComException; }    
            catch (DtsException ssisException)
            { throw ssisException; }
            catch (COMException COMException)
            { throw new COMException("ERROR : Package metadata is incomplete. Compile your package before trying again. DiscoverDFLMetaDataFLoop() exited with exception " + COMException.Message); }

            return count;
        } 
        #endregion
    }
}
